package com.servicesDirectory.dao.organisation;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.BaseProgrammeVO;
import com.servicesDirectory.common.vo.organisation.SupportingMaterialVO;

public class SupportingMaterialDAOImpl implements SupportingMaterialDAO {
	
	SupportingMaterialVO spm = null;
	ArrayList<SupportingMaterialVO> listspm = new ArrayList<SupportingMaterialVO>();
	protected static Connection conn = null;
	protected static void initConnection()
            throws SQLException, ClassNotFoundException {

        if (conn == null) {
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            String conUrl = "jdbc:sqlserver://localhost:0:1433;databaseName=MockPJ_ServiceDirectory;";
//            conn = DriverManager.getConnection(conUrl, "sa", "12345678");
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }


	@Override
	public boolean insert(SupportingMaterialVO spm) throws FunctionalException,
			SystemException {
		try {
			initConnection();
			Statement st = conn.createStatement();
			String sql = " INSERT INTO SupporttingMaterial VALUES ('"+spm.getURL()+"','"+spm.getDescription()+"','"+spm.getType()+"',1,6,'"+spm.getAddedDate()+"',1) ";
			
			int row = st.executeUpdate(sql);
			if(row>0)
			{
				return true;
			}
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(SupportingMaterialVO spm) throws FunctionalException,
			SystemException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<SupportingMaterialVO> listSPM(boolean include)
			throws FunctionalException, SystemException {
		String qr = null;
		
		
		try {
			initConnection();
			Statement st = conn.createStatement();
			if(include == true)
			{
				qr = " SELECT * FROM SupporttingMaterial ";
			}
			else
			{
				qr = "SELECT * FROM SupporttingMaterial WHERE IsActive = 1 ";
			}
			ResultSet rs = st.executeQuery(qr);
			while (rs.next()) {
				int spmID = rs.getInt(1);
				String url = rs.getString(2);
				String descrip = rs.getString(3);
				String type = rs.getString(4);
				String adddate = rs.getString(7);
				int active = rs.getInt(8);
				String stringActive;
				if (active == 1) {
					stringActive = "Yes";
				} else {
					stringActive = "No";
				}
				spm = new SupportingMaterialVO(spmID, url, descrip, type, adddate, active, stringActive);
				listspm.add(spm);
			}
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}
